package database;


import java.sql.*;
import java.util.ArrayList;

import model.Road;
import oracle.jdbc.pool.OracleDataSource;

public class RoadOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public RoadOperations()
	{
		conn=openDB();
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}
			
			conn = ods.getConnection();
			System.out.println("connected road.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// drop road table from database
	public void dropRoadTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence road_id_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);

				
			} 
			catch (SQLSyntaxErrorException e) 
			{
			
			}
			try
			{
			stm.execute("drop table CUSTOMER cascade constraints PURGE");
			System.out.println("customer table dropped.");
			}
			catch(SQLException ex)
			{
				
			}
			stm.execute("DROP TABLE Road");
			System.out.println("Road table dropped.");
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			//ex.printStackTrace();
		}
	}
	// create a road table in database
	public void CreateRoadTable() 
	{
		try 
		{
			stm = conn.createStatement();
			String create = "CREATE TABLE Road"
					+ "(road_id NUMBER PRIMARY KEY, est_id NUMBER, road_name VARCHAR(40),FOREIGN KEY(est_id) REFERENCES Estates1)";

			stm.executeUpdate(create);
			System.out.println("table created");
			// Creating a sequence
			String createseq = "create sequence road_id_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
		} 
		catch (SQLException e) 
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	
	// delete a road from table
	public void deleteRoad(int i) 
	{
		try 
		{
			String cmd = "DELETE FROM road WHERE road_id =" + i;
			stm = conn.createStatement();
			stm.executeUpdate(cmd);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// add a road to the table
	public void addRoad(Road e) 
	{
		try 
		{
			String sql = "INSERT INTO Road(road_id, est_id, road_name) VALUES (road_id_seq.nextVal,?,?)";

			prst = conn.prepareStatement(sql);
			prst.setInt(1, e.getEst_id());
			prst.setString(2, e.getName());

			prst.executeUpdate();
		} 
		catch (Exception se) 
		{
			System.out.println(se);
		}
	}
	// return a result set of all roads in table
	public ResultSet getRoad() 
	{
		try 
		{
			String queryString = "SELECT * FROM Road ORDER BY road_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	// get the estate id from its name
			public int getEstId(String name)
			{
				int a=-1;
				try 
				{
					String queryString = "SELECT est_name,est_id FROM Estates1 order by est_id";

					stm = conn.createStatement();
					rst = stm.executeQuery(queryString);
					while(rst.next())
					{
						if(rst.getString(1).equals(name))
						{
							a=rst.getInt(2);
						}
					}
				} 
				catch (SQLException e) 
				{
					System.out.println(e);
				}
				return a;
			}
	
			// get the road name from its id
			public String getRoadName(int i)
			{
				String n=null;
				try 
				{
					String queryString = "SELECT road_name from road where road_id="+i;

					stm = conn.createStatement();
					rst = stm.executeQuery(queryString);
					while(rst.next())
					{
						n=rst.getString(1);
					}
				} 
				catch (SQLException e) 
				{
					System.out.println(e);
				}
				return n;
			}
	
			
			// get the est name from its id
			public String getEstName(int i)
			{
				String n=null;
				try 
				{
					String queryString = "SELECT est_name from estates1 where est_id="+i;

					stm = conn.createStatement();
					rst = stm.executeQuery(queryString);
					while(rst.next())
					{
						n=rst.getString(1);
					}
				} 
				catch (SQLException e) 
				{
					System.out.println(e);
				}
				return n;
			}
	
			
	// update the name of a road
	public void updateRoadName(int i,String n) 
	{
		try 
		{
			String sql = "UPDATE Road SET road_name=" + "'" + n + "'" + "where road_id="
					+ i;

			stm = conn.createStatement();
			stm.executeUpdate(sql);
		} 
		catch (Exception e) 
		{
			System.out.println("Problem" + e);
		}
	}
	
	// returning added road
			public ResultSet getLastRow() 
			{
				String sqlStatement = "SELECT * FROM Road ORDER BY road_id";
				try {
					prst = conn.prepareStatement(sqlStatement,
							ResultSet.TYPE_SCROLL_INSENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
					rst = prst.executeQuery();
					rst.last();
					System.out.println(rst.getInt(1)+","+rst.getInt(2)+","+rst.getString(3));
				} 
				catch (Exception ex) 
				{
					System.out.println("ERROR: " + ex.getMessage());
				}
				
				return rst;
			}
	
	// return an array list of all road names
			public ArrayList<String> getRoadNames(String en) 
			{
				ArrayList<String> rds=new ArrayList<String>();
				try 
				{
					String queryString = "SELECT r.road_name from road r,estates1 e where r.est_id=e.est_id and e.est_name='"+en+"' order by r.road_id";
					stm = conn.createStatement();
					rst = stm.executeQuery(queryString);
					
					while(rst.next())
					{
						rds.add(rst.getString(1));
					}
				} 
				catch (Exception e) 
				{
					System.out.println("blah2");
					System.out.println(e);
				}
				return rds;
			}
		
	//get road id from the road name and est id
			public int getRoadId(int e,String r)
			{
				int a = -1;
				try 
				{
					String queryString = "SELECT * from Road where est_id="+e+" and road_name='"+r+"'";
					stm = conn.createStatement();
					rst = stm.executeQuery(queryString);
					
					while(rst.next())
					{
						a=rst.getInt(1);
					}
				} 
				catch (Exception ee) 
				{
					System.out.println(ee);
				}
				return a;
			}
			
	// close the connection to database
	public void closeDB() 
	{
		try 
		{
			stm.close();
			rst.close();
			conn.close();
			System.out.print("Connection closed road");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
}
